package com.liferay.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liferay.dao.FacultyDao;
import com.liferay.dao.ConnectDao;
import com.liferay.model.Faculty;
import com.liferay.model.Group;
import com.liferay.model.Subject;

public class FacultyDaoImpl implements FacultyDao {

	private static final String GET_ALL_FACULTY = "SELECT n.idFaculty, n.nameFaculty, n.fkgroup  FROM nlu_faculty n;";
	private static final String GET_SUBJECT_BY_FACULTY = "SELECT idSubject,nameSubject,numberOfCredit FROM nlu_subject as s,nlu_faculty as f WHERE f.idFaculty = s.fkFaculty AND s.fkFaculty = ?;";
	private static final String INSERT_SUBJECT_BY_FACULTY = "INSERT INTO nlu_subject(nameSubject,numberOfCredit,fkFaculty) VALUES(?,?,?);";
	private static final String DELETE_SUBJECT_BY_ID = "DELETE FROM nlu_subject WHERE nlu_subject.idSubject =?;";
	private static final String GET_SUBJECT_BY_ID = "SELECT * FROM nlu_subject WHERE nlu_subject.idSubject=?";
	private static final String UPDATE_SUBJECT_BY_ID = "UPDATE nlu_subject SET nlu_subject.nameSubject = ?,nlu_subject.numberOfCredit=? WHERE nlu_subject.idSubject = ?";
	private PreparedStatement pre;
	private ResultSet rs;
	private List<Faculty> listFaculty;
	private List<Subject> listSubject;
	private Connection conn;
	private Faculty fac;
	private Subject sub;

	public static void main(String[] args) {
		FacultyDaoImpl facu = new FacultyDaoImpl();
		// Subject subs = new Subject();
		// System.out.println(facu.getAllFaculty());
		// System.out.println(facu.getSubjectByFaculty(3));
		// System.out.println(facu.insertSubjectByFaculty(new Subject(subs
		// .getIdSubject(), "ABC", 3, 2)));
		System.out.println(facu.deleteSubjectById(11026));
	}

	@Override
	public List<Faculty> getAllFaculty() {
		conn = ConnectDao.getConnection();
		listFaculty = new ArrayList<Faculty>();
		try {
			pre = conn.prepareStatement(GET_ALL_FACULTY);
			rs = pre.executeQuery();
			while (rs.next()) {
				fac = new Faculty();
				fac.setIdFaculty(rs.getLong("idFaculty"));
				fac.setNameFaculty(rs.getString("nameFaculty"));
				fac.setGroup(rs.getInt("fkgroup"));
				listFaculty.add(fac);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listFaculty;
	}

	@Override
	public List<Subject> getSubjectByFaculty(long fkFaculty) {
		conn = ConnectDao.getConnection();
		listSubject = new ArrayList<Subject>();
		try {
			pre = conn.prepareStatement(GET_SUBJECT_BY_FACULTY);
			pre.setLong(1, fkFaculty);
			rs = pre.executeQuery();
			while (rs.next()) {
				sub = new Subject();
				sub.setIdSubject(rs.getLong("idSubject"));
				sub.setNameSubject(rs.getString("nameSubject"));
				sub.setNumberOfCredit(rs.getInt("numberOfCredit"));
				sub.setFkFaculty(fkFaculty);
				listSubject.add(sub);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listSubject;
	}

	@Override
	public boolean insertSubjectByFaculty(Subject subject) {
		listSubject = new ArrayList<Subject>();
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(INSERT_SUBJECT_BY_FACULTY);
			pre.setString(1, subject.getNameSubject());
			pre.setInt(2, subject.getNumberOfCredit());
			pre.setLong(3, subject.getFkFaculty());
			pre.executeUpdate();
			pre.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteSubjectById(long id) {
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(DELETE_SUBJECT_BY_ID);
			pre.setLong(1, id);
			pre.executeUpdate();
			pre.close();
			ConnectDao.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean editFacult(Faculty faculty) {
		conn = ConnectDao.getConnection();
		try {
			String sql = "UPDATE nlu_faculty uf SET uf.nameFaculty=?, uf.fkgroup=? WHERE uf.idFaculty=?;";
			pre = conn.prepareStatement(sql);
			pre.setString(1, faculty.getNameFaculty());
			pre.setInt(2, faculty.getGroup());
			pre.setLong(3, faculty.getIdFaculty());
			pre.executeUpdate();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public List<Group> getGroup() {
		String sql = "SELECT id,name from nlu_group";
		conn = ConnectDao.getConnection();
		List<Group> list = new ArrayList<Group>();
		try {
			pre = conn.prepareStatement(sql);
			ResultSet res = pre.executeQuery();
			while (res.next()) {
				Group group = new Group();
				group.setId(res.getInt("id"));
				group.setName(res.getString("name"));
				list.add(group);
			}
			res.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public boolean addFaculty(Faculty faculty) {
		String sql = "INSERT INTO nlu_faculty(nameFaculty,fkgroup) VALUES(?,?);";
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(sql);
			pre.setString(1, faculty.getNameFaculty());
			pre.setInt(2, faculty.getGroup());
			pre.executeUpdate();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean deleteFaculty(Faculty faculty) {
		String sql = "DELETE FROM nlu_faculty WHERE idFaculty=?;";
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(sql);
			pre.setLong(1, faculty.getIdFaculty());
			pre.executeUpdate();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	
	
	@Override
	public boolean updateSubjectById(String nameSubject, int numberOfCredit,
			long idSubject) {
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(UPDATE_SUBJECT_BY_ID);
			pre.setString(1, nameSubject);
			pre.setInt(2, numberOfCredit);
			pre.setLong(3, idSubject);
			int i = 0;
			i = pre.executeUpdate();
			pre.close();
			ConnectDao.close();
			if (i > 0)
				return true;
			else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	@Override
	public List<Subject> getSubjectById(long id) {
		listSubject = new ArrayList<Subject>();
		conn = ConnectDao.getConnection();
		try {
			sub = new Subject();
			pre = conn.prepareStatement(GET_SUBJECT_BY_ID);
			pre.setLong(1, id);
			rs = pre.executeQuery();
			while (rs.next()) {
				sub.setIdSubject(rs.getLong("idSubject"));
				sub.setNameSubject(rs.getString("nameSubject"));
				sub.setNumberOfCredit(rs.getInt("numberOfCredit"));
				listSubject.add(sub);
			}
			pre.close();
			rs.close();
			ConnectDao.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listSubject;
	}

}
